/**
 * gislin 2017年7月20日
 */
package com.btcode.db.executer;

import com.btcode.common.ExecuteTimeUtil;
import com.btcode.db.config.DataBaseConfig;
import com.btcode.db.connection.IConnectionGetter;
import com.btcode.db.result.Row;
import com.btcode.db.sqlparameter.SQLParameterList;
import com.btcode.exception.MsgException;
import com.btcode.log.ILog;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map.Entry;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author gislin 2017年7月20日
 */
public abstract class BaseDataExecuter implements IDataExecuter {

    private IConnectionGetter connectionGetter;
    private DataBaseConfig config;
    private ILog log;

    public Connection getCon() {
        return connectionGetter.getConnection();
    }

    public BaseDataExecuter(DataBaseConfig config, IConnectionGetter connectionGetter) {
        this.connectionGetter = connectionGetter;
        this.config = config;
    }

    @Override
    public List<Row> queryList(String sql, SQLParameterList parms) {
        List<Row> rows = executeQuery(sql, parms);
        return rows;
    }

    @Override
    public Object queryScalar(String sql, SQLParameterList parms) {
        List<Row> rows = executeQuery(sql, parms);
        if (rows.size() < 1) {
            return null;
        }
        Row row = rows.get(0);
        Set<Entry<String, Object>> entry = row.entrySet();

        for (Entry<String, Object> e : entry) {
            return e.getValue();
        }

        return null;
    }

    @Override
    public void setLog(ILog log) {
        this.log = log;
    }

    @Override
    public DataBaseConfig getDataBaseConfig() {
        return config;
    }

    @Override
    public void create(String sql, SQLParameterList parms) {

        sql = sql.trim();

        if (!sql.startsWith("insert") && !sql.startsWith("INSERT")) {
            throw new MsgException("create方法只能执行insert语句");
        }

        executeUpdate(sql, parms);
    }

    @Override
    public void update(String sql, SQLParameterList parms) {

        sql = sql.trim();

        if (!sql.startsWith("update") && !sql.startsWith("UPDATE")) {
            throw new MsgException("update方法只能执行update语句");
        }

        executeUpdate(sql, parms);
    }

    @Override
    public void delete(String sql, SQLParameterList parms) {

        sql = sql.trim();

        if (!sql.startsWith("delete") && !sql.startsWith("DELETE")) {
            throw new MsgException("delete方法只能执行delete语句");
        }

        executeUpdate(sql, parms);
    }

    public void ddl(String sql) {
        executeUpdate(sql, null);
    }

    /**
     * 把带有占位符的sql处理成jdbc中通用的sql 并构建查询参数 gislin 2017年7月21日
     *  isQuery的作用
     *  在数据量大的情况下，需要预先知道记录集的大小，这个时候要求ResultSet可以先游标到末尾，然后取得记录数用来初始化List，这样可以避免逐条增加List的Item导致CPU和内存暴增
     *  所以在查询的情况下，需要对ResultSet的一些模式进行修改，使之支持这种方式
     */
    protected SQLAndParameter buildSQLAndParameter(String sql, SQLParameterList parameters,
                                                   Connection conn,boolean isQuery) {

        try {
            String regEx = "@(.+?)[\\s,)$]";
            Pattern p = Pattern.compile(regEx);
            Matcher m = p.matcher(sql + " ");

            List<Object> parameterList = new ArrayList<Object>();
            PreparedStatement preparedStatement;

            String finalSQL = sql;

            while (m.find()) {
                String parameterName = m.group().replaceFirst("@", "").trim().replace(",", "")
                        .replace(")", "");
                Object value = parameters.get(parameterName);
                parameterList.add(value);

                finalSQL = finalSQL.replaceFirst("@" + parameterName, "?");
            }


            preparedStatement = isQuery?conn.prepareCall(finalSQL,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY):conn.prepareStatement(finalSQL);

            for (int i = 0; i < parameterList.size(); i++) {
                preparedStatement.setObject(i + 1, parameterList.get(i));
            }

            SQLAndParameter sqlAndParameter = new SQLAndParameter(finalSQL, preparedStatement);

            return sqlAndParameter;
        }
        catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public void executeUpdateBatch(String sql, List<SQLParameterList> params) {
        Connection con = null;
        SQLAndParameter sp = null;
        try {
            con = getCon();
            sp = new SQLAndParameter(sql, params.get(0), con);
            for (int i = 0; i < params.size(); i++) {
                SQLParameterList p = params.get(i);
                sp.addBatchParameter(p);
            }

            log.info("SQL:" + sql + ",参数:" + params.toString());

            ExecuteTimeUtil executeTime = new ExecuteTimeUtil();

            sp.getParameter().executeBatch();

            log.info("SQL:" + sql + ",执行时间：" + executeTime.getLastDiff());

        }
        catch (Exception e) {
            log.logOn();
            log.error("SQL:" + sql + ",执行出错：" + params.toString(), e);
            log.logOff();
            throw new MsgException("批量SQL执行失败,参数:" + params.toString(), e);
        }
        finally {
            try {
                if (sp != null) {
                    sp.getParameter().close();
                }
            }
            catch (SQLException e) {
                log.logOn();
                log.error("SQL:" + sql + ",执行出错：" + params.toString(), e);
                log.logOff();
                throw new RuntimeException(e);
            }
        }
    }

    protected void executeUpdate(String sql, SQLParameterList parameters) {
        if (parameters == null) {
            parameters = new SQLParameterList();
        }
        Connection con = null;
        SQLAndParameter sqlAndParameter = null;
        try {
            con = getCon();
            sqlAndParameter = buildSQLAndParameter(sql, parameters, con,false);
            log.info("SQL:" + sql + ",参数:" + parameters.toString());
            ExecuteTimeUtil executeTime = new ExecuteTimeUtil();
            sqlAndParameter.getParameter().executeUpdate();

            log.info("SQL:" + sql + ",执行时间：" + executeTime.getLastDiff());

        }
        catch (Exception e) {
            log.logOn();
            log.error("SQL:" + sql + ",执行出错："+ ",参数:" + parameters.toString(), e);
            log.logOff();
            throw new RuntimeException("数据库执行出错,参数:" + parameters.toString(), e);
        }
        finally {
            try {
                if (sqlAndParameter != null) {
                    if (sqlAndParameter.getParameter() != null) {
                        sqlAndParameter.getParameter().close();
                    }
                }
                if (con != null && con.getAutoCommit()) {
//                if (con != null) {
                    con.close();
                }
            }
            catch (Exception e2) {
                log.logOn();
                log.error("SQL:" + sql + ",执行出错：", e2);
                log.logOff();
                throw new RuntimeException(e2);
            }
        }
    }

    protected List<Row> executeQuery(String sql, SQLParameterList parameters) {
        ResultSet resultSet = null;
        if (parameters == null) {
            parameters = new SQLParameterList();
        }
        Connection con = null;
        SQLAndParameter sqlAndParameter = null;
        try {
            con = getCon();
            sqlAndParameter = buildSQLAndParameter(sql, parameters, con,true);
            log.info("SQL:" + sql + ",参数:" + parameters.toString());

            ExecuteTimeUtil executeTime = new ExecuteTimeUtil();
            resultSet = sqlAndParameter.getParameter().executeQuery();
            log.info("SQL:" + sql + ",执行时间：" + executeTime.getLastDiff());

            List<Row> rows = buildRows(resultSet,true);
            return rows;
        }
        catch (Exception e) {
            log.logOn();
            log.error("SQL:" + sql + ",执行出错："+ ",参数:" + parameters.toString(), e);
            log.logOff();
            throw new RuntimeException("数据库执行出错,参数:" + parameters.toString(), e);
        }
        finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (sqlAndParameter != null) {
                    if (sqlAndParameter.getParameter() != null) {
                        sqlAndParameter.getParameter().close();
                    }
                }
                if (con != null) {
                    //解决分布式事务提交失败的问题，如果这里不做判断，会导致之前的修改失败
                    if (con.getAutoCommit()) {
                        con.close();
                    }
                }
            }
            catch (Exception e2) {
                log.logOn();
                log.error("SQL:" + sql + ",执行出错：", e2);
                log.logOff();
                throw new RuntimeException(e2);
            }
        }
    }

    /**
     *
     * @param resultSet
     * @param canScrollInsensitive 是否支持游标来回移动，sqlite不支持
     * @return
     * @throws SQLException
     */
    protected List<Row> buildRows(ResultSet resultSet,boolean canScrollInsensitive) throws SQLException {
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        List<Row> rows;

        if(canScrollInsensitive){
            resultSet.last();
            rows = new ArrayList<>(resultSet.getRow());
            resultSet.beforeFirst();
        }
        else {
            rows = new ArrayList<>();
        }

        Object value;
        Row row;

        while (resultSet.next()) {
            row = new Row();
            for (int i = 0; i < columnCount; i++) {
                value = resultSet.getObject(i + 1);
                row.put(metaData.getColumnLabel(i + 1).toUpperCase(), value);
            }
            rows.add(row);
        }

        return rows;
    }

    @Override
    public void beginTransaction() {
        Connection con;
        try {
            con = getCon();
            con.setAutoCommit(false);

            TransactionInfo transactionInfo = MulitTransactionControl.getInstance()
                    .beginTransaction();

            if (transactionInfo.getTransactionIndex() == 1) {
                log.info("首次开启事务," + transactionInfo.toString());
            }
            else {
                log.info("开启事务," + transactionInfo.toString());
            }
        }
        catch (SQLException e) {
            log.logOn();
            log.info("事务开启失败", e);
            log.logOff();
            throw new RuntimeException(e);
        }
    }

    @Override
    public void commit() {
        Connection con;
        try {
            con = getCon();

            String curTransactionInfo = MulitTransactionControl.getInstance().getCurrentTransaction().toString();

            TransactionInfo transactionInfo = MulitTransactionControl.getInstance()
                    .commitTransaction();

            if (transactionInfo.isCommitAble()) {
                con.commit();
                con.setAutoCommit(true);
                /**
                 * 修复连接泄露的问题
                 */
                con.close();
                log.info("提交最终事务," + curTransactionInfo);
            }
            else {
                log.info("分布提交事务," + curTransactionInfo);
            }

        }
        catch (SQLException e) {
            log.logOn();
            log.info("事务提交失败", e);
            log.logOff();
            throw new RuntimeException(e);
        }
    }

    @Override
    public void close() {
        if (connectionGetter != null) {
            this.connectionGetter.close();
        }
    }

    @Override
    public void logOn() {
        log.logOn();
    }

    @Override
    public void logOff() {
        log.logOff();
    }

    @Override
    public void rollback() {
        Connection con;
        try {
            con = getCon();
            if (con.getAutoCommit()) {
                return;
            }
            con.rollback();
            con.setAutoCommit(true);
            con.close();
            MulitTransactionControl.getInstance().rollback();
            log.info("回滚事务");
        }
        catch (SQLException e) {
            log.logOn();
            log.info("事务回滚失败", e);
            log.logOff();
            throw new RuntimeException(e);
        }
    }
}
